package com.yazdankhah.dal;

import com.yazdankhah.utils.TableUtil;
import java.lang.reflect.Field;
import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * This class is responsible for all data access for the application and contains generic methods
 * used in the bean manager classes. Each bean managers contains specific methods to deal with the
 * corresponding tables. It also deal with transaction processing to change multiple tables data
 * consistently.
 *
 * @author Ahmad Yazdankhah
 */
public class SqlBak {

//...................... P R I V A T E   M E T H O D S ......................//
//...................... P U B L I C   M E T H O D S ........................//
  public static Connection getConn() {

    try {
      Properties props = new Properties();
      //props.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.cosnaming.CNCtxFactory");
      props.setProperty(Context.PROVIDER_URL, "jdbc:mysql://localhost:3306/qt");
      
      Context ctx = new InitialContext(props);
      
      DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/qtDatasource");
      return ds.getConnection();
      
    } catch (NamingException | SQLException ex) {
      System.err.println("No connection due to: " + ex.getMessage());
      return null;
    }

      /*
       * ............ Using Ordinary Connection .............
       */
//        String driverStr = "com.mysql.jdbc.Driver";
//        String urlStr = "jdbc:mysql://localhost:3306/qt";
//        String uid = "root";
//        String pwd = "sesame";
//
//        try {
//            Class.forName(driverStr);
//            return DriverManager.getConnection(urlStr, uid, pwd);
//
//        } catch (SQLException | ClassNotFoundException ex) {
//            System.err.println(ex.getMessage());
//            return null;
//        }

  } //method


  /**
   * Create a connection object, turn of the auto-commit, and return it.
   *
   * @return the connection object to the database.
   */
  public static Connection getTransactionConn() {
    try {
      /*
       * ............ Using Connection Pool .................
       * Note that use this if you run the application from client point of
       * view. It means, you should run the jsp pages but if you want to test
       * SqlBak, use the below part.
       */
            //        return pool.getConnection();
      //        return pool.getConnection();
        /*
       * ............ Using Ordinary Connection .............
       */
      Connection con = getConn();
      con.setAutoCommit(false);
      return con;
    } //method
    catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * Close the give connection. Set the auto-commit to true before closing.
   *
   * @param con - the connection to database object.
   * @param result
   */
  public static void closeTransactionConnection(
          Connection con, boolean result) {

    try {
      if (con != null) {

        if (result) {
          con.commit();
        } else {
          con.rollback();
        }

        con.setAutoCommit(true);
        con.close();
      }

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
    }

  } //method


  public static void freeResource(Statement s) {
    try {
      if (s != null) {
        s.close();
      }

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
    }

  } //method


  public static void freeResource(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
    }

  } //method


  public static void freeResource(Connection con) {
    try {
      if (con != null) {
        con.close();
      }

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
    }

  } //method


  public static void freeResource(PreparedStatement ps) {
    try {
      if (ps != null) {
        ps.close();
      }

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
    }

  } //method


  /**
   * make an arrayList and put the sb set contents as beans into it.
   *
   * @param <E>
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs - the sb set
   * @param c  - the class of the bean
   *
   * @return the arrayList
   */
  public static <E> List<E> resultSetToArrayList(ResultSet rs, Class<? extends E> c) {

    try {
      List<E> arr = new ArrayList<>();

      while (rs.next()) {
        E bean = c.newInstance();
        beanMakerEngine(rs, bean);
        arr.add(bean);

      } //while

      return arr;

    } catch (SQLException | InstantiationException | IllegalAccessException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * This is for single record return queries. Retrieve the single record and pack it as a bean an
   * return it.
   *
   * @param <E>
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs - the sb set
   * @param c  - the class of the bean
   *
   * @return the object
   */
  public static <E> E resultSet2Obj(ResultSet rs, Class<? extends E> c) {

    try {
      //If resultSet is null, it returns an empty bean.
      if (rs == null) {
        return c.newInstance();
      }

      rs.next();
      E bean = c.newInstance();

            //check if the rs has at least one row.
      //If not, it returns an empty bean.
      if (rs.getRow() != 0) {
        beanMakerEngine(rs, bean);
      }

      return bean;

    } catch (SQLException | InstantiationException |
            IllegalAccessException ex) {

      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * get the bean fields types.
   *
   * @param e - the bean object.
   */
  private static <E> List<String> getBeanFieldType(E bean) {

    List<String> arr = new ArrayList<>();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      field.setAccessible(true);
      String type = field.getType().toString();
      arr.add(type);

    } //for

    return arr;

  } //method

//    private void testgetBeanFieldType() {
//        Recipe r = new Recipe("Good Name", "Every Thing Mixed",
//                "Every thing", 1, "image");
//
//        ArrayList<String> arr = getBeanFieldType(r);
//
//        System.out.println(arr.toString());
//
//    } //method.

  /**
   * Get the bean fields values.
   *
   * @param e - the bean object.
   */
  private static <E> List<String> getBeanFieldValue(E bean) {

    List<String> arr = new ArrayList<>();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      field.setAccessible(true);
      try {
        Object value = field.get(bean);

        if (value == null) {
          arr.add("null");
        } else {
          arr.add(value.toString());
        }
      } catch (IllegalAccessException ex) {
        System.err.println("SQL exception due to: " + ex.getMessage());
      }

    } //for

    return arr;

  } //method

//    private void testgetBeanFieldValue() {
//        Recipe r = new Recipe("Good Name", "Every Thing Mixed",
//                "Every thing", 1, "image");
//
//        ArrayList<String> arr = getBeanFieldValue(r);
//
//        System.out.println(arr.toString());
//
//    } //method.

  /**
   * make the where clause for delete.
   *
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the type array
   *
   * @return the where clause.
   */
  private static String createWHEREClause(List<String> nameArr,
                                          List<String> valueArr, List<String> typeArr) {
    StringBuilder result = new StringBuilder();
    String tempStr;

    for (int i = 0; i < valueArr.size(); i++) {
      // remove \' from the char types
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "\'"));

      if (valueArr.get(i).equalsIgnoreCase("null")) {
        continue;
      } else {
        if (typeArr.get(i).equalsIgnoreCase("int")
            && Integer.valueOf(valueArr.get(i)) < 0) {
          continue;
        } else {
          if (!typeArr.get(i).equalsIgnoreCase("int")) {
            tempStr = String.format("AND   %s = \'%s\'\n",
                    nameArr.get(i), valueArr.get(i));
          } else {
            tempStr = String.format("AND   %s = %s\n",
                    nameArr.get(i), valueArr.get(i));
          }
        }
      }

      result.append(tempStr);

    } //for

    if (result.toString().isEmpty()) {
      return null;
    } else {
      return result.toString().substring(6);
    }

  } //method


  /**
   * create DELET statement
   *
   * @param tabName  - the table name.
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the type array
   *
   * @return the delete statement.
   */
  private static String createDELETEStatement(
          String tabName, List<String> nameArr,
          List<String> valueArr, List<String> typeArr) {
    String deleteStr;
    String whereClauseStr = createWHEREClause(nameArr, valueArr, typeArr);

    if (whereClauseStr != null) {
      deleteStr = String.format("DELETE FROM %s \nWHERE %s",
              tabName, whereClauseStr);
    } else {
      deleteStr = String.format("DELETE FROM %s", tabName);
    }

    return deleteStr;

  } //end of method


  private static void testcreateDELETEStatement() {
    String tabName = "test";
    List<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    List<String> valueArr = new ArrayList<>();
    valueArr.add("-25");
    valueArr.add("null");
    valueArr.add("-34");

    List<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("String");
    typeArr.add("int");

    System.out.println(
            createDELETEStatement(tabName, nameArr, valueArr, typeArr));

  } //method


  /**
   * get a bean and delete the corresponding record from the table.
   *
   * @param <E>
   * @param bean
   *
   * @return true if the delete operation is successful and false otherwise.
   */
  public static <E> boolean beanDeleteEngine(E bean) {

    String tabName = bean.getClass().getSimpleName();

    List<String> nameArr = getBeanFieldName(bean);
    List<String> typeArr = getBeanFieldType(bean);
    List<String> valueArr = getBeanFieldValue(bean);

    String stmnt = createDELETEStatement(
            tabName, nameArr, valueArr, typeArr);
    //Test: System.out.println(stmnt);

    int result = executeUpdate(stmnt);
    
    return result != -1;

  } //method

//    private void testbeanDeleteEngine() {
//        Comment r = new Comment(110, null, -1);
//
//        boolean sb = beanDeleteEngine(r);
//
//        System.out.println(sb);
//
//    } //method.

  /**
   * encrypt password.
   *
   * @param pass - the password that should be encrypted.
   *
   * @return the encrypted password.
   */
//    public static String encryptPass(String pass) {
//        try {
//            byte[] bytesOfMessage = pass.getBytes("UTF-8");
//            MessageDigest md = MessageDigest.getInstance("MD5");
//            byte[] md5hash = md.digest(bytesOfMessage);
//
//            BigInteger i = new BigInteger(1, md5hash);
//
//            return String.format("%1$032X", i);
//
//        } catch (UnsupportedEncodingException | NoSuchAlgorithmException e) {
//            System.err.println(e.getMessage());
//            return null;
//        }
//
//    } //method
//
//
//    private static void testencryptPass() {
//        String pass = ""
//            + "Josh4";
//
//        System.out.println("[" + encryptPass(pass) + "]");
//
//    } //method.
//
//
//    private static void testupdateEncryptPass() {
//        String pass = "Josh5";
//        String encrypedPass = encryptPass(pass);
//
//        /*
//         * .............. DIRECT UPDATE ................................
//         */
//
////        System.out.println("[" + encrypedPass + "]");
////        String stmnt = String.format(
////                "UPDATE user SET userPass = \'%s\' WHERE id = %d",
////                encrypedPass, 9);
////        int sb = executeUpdate(stmnt);
////        System.out.println(sb);
//
//        /*
//         * ............. USING AHMAD'S ENGINE ..........................
//         */
//
////        User u = new User(9, "Ronald", encrypedPass, "ron@gmail.com", "120.jpg");
////        encrypedPass = encrypedPass.replace("[']", "\'");
////
////        System.out.println("[" + encrypedPass + "]");
////
////        System.out.println(updateUserByID(u));
//
//    } //method.
  /**
   * Insert a given bean into the appropriate table.
   *
   * @param <E>
   * @param bean - the given bean object.
   *
   * @return true if the insert is successful and false otherwise.
   */
  public static <E> boolean beanInsertEngine(E bean) {

    Connection con = getConn();
    boolean result = beanInsertEngine(con, bean);
    freeResource(con);

    return result;

  } //method


  /**
   * A generic method to check if an attribute exists?
   *
   * @param sqlStr - the SQL string
   * @param a      - the first parameter
   *
   * @return true if the attribute exists and false otherwise
   */
  public static boolean existanceChaeckByStrAttrib(
          String sqlStr, String a) {

    if (a == null || a.isEmpty()) {
      return false;
    }

    ResultSet rs = null;

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      pstmt.setString(1, a);
      rs = pstmt.executeQuery();

      return rs.next();

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return false;

    } finally {
      freeResource(rs);
    }

  } //method


  /**
   * Count number of records of a table by one attribute
   *
   * @param sqlStr - the SQL string
   * @param parArr
   *
   * @return the count number
   */
  public static Integer countBeansByVarIntAttribs(
          String sqlStr, int... parArr) {

    ResultSet rs = null;

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      setAttribs(pstmt, index, parArr);
      rs = pstmt.executeQuery();
      rs.next();

      return rs.getInt(1);

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  /**
   * Count number of records of a table by one attribute
   *
   * @param sqlStr - the SQL string
   *
   * @return the count number
   */
  public static Integer countBeansByNoAttribute(String sqlStr) {

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);
            ResultSet rs = pstmt.executeQuery();) {

      rs.next();

      return rs.getInt(1);

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;

    }

  } //method


  /**
   * Get the last inserted ID. Note that it works fine provided that the database connection for the
   * last insertion is not changed. That's why this method need the connection.
   *
   * @param con - the database connection object
   *
   * @return the last inserted ID
   */
  private static int getLastInsertedID(Connection con) {

    String sqlStr = "SELECT last_insert_id()";

    try (
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sqlStr);) {

      rs.next();
      int result = rs.getInt(1);
      rs.close();
      return result;

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return -1;
    }

  } //method


  /**
   * Insert a given PK bean and return the last inserted auto increment id. the transaction is not
   * committed.
   *
   * @param <E>
   * @param con
   * @param bean - the given bean object
   *
   * @return the last auto increment id inserted or -1 if unsuccessful
   */
  public static <E> int beanPKInsertEngine(Connection con, E bean) {

    boolean result = beanInsertEngine(con, bean);

    if (result) {
      return getLastInsertedID(con);
    } else {
      return -1;
    }

  } //method


//    private static void testbeanInsertEngine2() {
//        
//        Connection con  = getSingleConn();
//        String tabName  = "Component";
//        
//        String compName = "comp2";
//        String compType = "Software";
//        Component c = new Component(compName, compType);
//
//        System.out.println(beanInsertEngine(con, c));
//        
//        Table.printTable(con, tabName);
//    } //method
  /**
   * Insert a given FK array list of beans into the appropriate tables.
   *
   * @param <E>
   * @param con
   * @param beanArr - the given bean object array
   *
   * @return true if the insert is successful and false otherwise
   */
  public static <E> boolean beanFKInsertEngine(
          Connection con, ArrayList<E> beanArr) {

    for (E bean : beanArr) {
      boolean result = beanInsertEngine(con, bean);
      if (!result) {
        return false;
      }
    }

    return true;

  } //method


//    private static void testbeanInsertEngine3() {
//        
//        Connection con  = getSingleConn();
//        String tabName  = "Component";
//        
//        String compName1 = "comp4";
//        String compType1 = "Software";
//        Component c1 = new Component(compName1, compType1);
//        
//        String compName2 = "comp5";
//        String compType2 = "Software";
//        Component c2 = new Component(compName2, compType2);
//        
//        ArrayList<Component> beanArr = new ArrayList();
//        beanArr.add(c1);
//        beanArr.add(c2);
//        
//        System.out.println(beanInsertEngine(con, beanArr));
//        
//        Table.printTable(con, tabName);
//    } //method
  /**
   * Insert master/detail data into tables in one transaction.
   *
   * @param <E>     - the variable for master bean
   * @param <F>     - the variable for detail bean
   * @param bean    - the given master bean
   * @param beanArr - the given detail array
   *
   * @return true if all inserts are successful and false otherwise
   */
  public static <E, F> boolean transactionPKFKbeanInsertEngine(
          E bean, ArrayList<F> beanArr) {

    try {
      Connection con = getConn();
      con.setAutoCommit(false);
      Savepoint savP = con.setSavepoint();

      //inserting PK
      if (!beanInsertEngine(con, bean)) {
        con.rollback(savP);
        con.releaseSavepoint(savP);
        return false;
      }

      //inserting FK
      if (!beanInsertEngine(con, beanArr)) {
        con.rollback(savP);
        con.releaseSavepoint(savP);
        return false;
      }

      con.commit();
      con.releaseSavepoint(savP);
      con.setAutoCommit(true);
      return true;

    } //method
    catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return false;
    }
  } //method


  private static void testtransactionPKFKbeanInsertEngine() {
//        String compName = "comp4";
//        String compType = "Software";
//        Component c = new Component(compName, compType);
//        
//        String compName2 = "comp5";
//        String compType2 = "Hardware";
//        Component c2 = new Component(compName2, compType2);
//        
//        String compName1 = "comp4";
//        String compType1 = "Software";
//        Component c = new Component(compName1, compType1);
//        
//        String compName2 = "comp5";
//        int compType2    = 2;
//        Component c2 = new Component(compName2, compType2);
//        
//        ArrayList<Component> beanArr = new ArrayList();
//        beanArr.add(c1);
//        beanArr.add(c2);
//        
//        System.out.println(beanInsertEngine(con, beanArr));
//        
//        Table.printTable(con, tabName);
  } //method


  /**
   * A generic method for executeUpdate
   *
   * @param sqlStr - the SQL string
   *
   * @return the number of records affected or -1 if unsuccessful
   */
  public static int executeUpdate(String sqlStr) {

    Connection con = getConn();

    int result = executeUpdate(con, sqlStr);
    freeResource(con);

    return result;

  } //method


  /**
   * executeUpdate a statement
   *
   * @param con   - the connection object
   * @param stmntStr
   *
   * @return - the number of rows affected
   */
  public static int executeUpdate(Connection con, String stmntStr) {

    try {
      try (Statement stmnt = con.createStatement()) {
        return stmnt.executeUpdate(stmntStr);
      }

    } catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return -1;
    }

  } //method


  /**
   * A generic method to execute a given SQL statement with one integer attribute.
   *
   * @param sqlStr - the given SQL string
   * @param parArr
   *
   * @return the number of records affected
   */
  public static int executeUpdateByVarIntAttribs(
          String sqlStr, int... parArr) {

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      setAttribs(pstmt, index, parArr);
      return pstmt.executeUpdate();

    } catch (SQLException e) {
      //e.printStackTrace();
      return -1;

    }

  } //method


  /**
   * A generic method to execute a given SQL statement with two integer attributes.
   *
   * @param con
   * @param sqlStr - the given SQL string
   * @param parArr
   *
   * @return the number of records affected
   */
  public static int executeUpdateByVarStrAttribs(
          Connection con, String sqlStr, String... parArr) {

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      setAttribs(pstmt, index, parArr);
      return pstmt.executeUpdate();

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return -1;
    }

  } //method


  /**
   * A generic method to execute a given SQL statement with two integer attributes.
   *
   * @param con    - the connection object
   * @param sqlStr - the given SQL string
   * @param parArr - the array of the integer attributes
   *
   * @return the number of records affected
   */
  public static int executeUpdateByVarIntAttribs(
          Connection con, String sqlStr, int... parArr) {

    try (
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      setAttribs(pstmt, index, parArr);
      return pstmt.executeUpdate();

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return -1;
    }

  } //method


  /**
   * Show master/master objects data
   *
   * @param <M>   - the master part object
   * @param <D>   - the detail part object
   * @param mdArr
   */
  public static <M, D> void showMastreDetailObjectsArr(
          List<MasterDetail<M, D>> mdArr) {

    for (MasterDetail<M, D> md : mdArr) {

      System.out.println("\nMaster = " + md.getMaster());
      List<D> dArr = md.getDetailArr();

      for (D d : dArr) {
        System.out.println("Detail = " + d);
        System.out.println("...........................");
      } //inner-for

      System.out.println("##############################################");

    } //outer-for

  } //method


  /**
   * Search master/detail
   * @throws java.lang.NoSuchFieldException
   * @throws java.lang.IllegalAccessException
   * @throws java.sql.SQLException
   *
   * @precondition: the pk and fk id are integers.
   *
   * @param <M>         - the variable for master bean
   * @param <D>         - the variable for detail bean
   * @param mSQLStr     - SQL string for the master part
   * @param dSQLStr     - SQL string for the detail part
   * @param attribStr   - the attribute value on master table
   * @param mc          - the master class name
   * @param dc          - the detail class name
   * @param fkFieldName - the foreign key field name in master
   *
   * @return an array list of the MasterDetailObject
   */
//  public static <M, D> List<MasterDetail<M, D>> getMasterDetailsByOneAttribute(
//          String mSQLStr, String dSQLStr, String attribStr,
//          Class<? extends M> mc, Class<? extends D> dc,
//          String fkFieldName)
//          throws NoSuchFieldException, IllegalArgumentException,
//                 IllegalAccessException, SQLException {
//
//    List<MasterDetail<M, D>> mdArr = new ArrayList<>();
//
//    try (Connection con = getSingleConn()) {
//      List<M> mArr = getBeans(con, mSQLStr, mc, attribStr);
//
//      for (M mBean : mArr) {
//
//        Field fkField = mBean.getClass().getDeclaredField(fkFieldName);
//        fkField.setAccessible(true);
//        int fkValue = (Integer) fkField.get(mBean);
//        List<D> dArr = getBeans(con, dSQLStr, dc, fkValue);
//        mdArr.add(new MasterDetail<M, D>(mBean, dArr));
//
//      } //for
//    } //try
//
//    return mdArr;
//  } //method


  /**
   * Search master/detail data from master/detail tables.
   *
   * @throws java.lang.NoSuchFieldException
   * @throws java.lang.IllegalAccessException
   * @throws java.sql.SQLException
   * @precondition: the pk and fk id are integers.
   *
   * @param <M>         - the variable for master bean
   * @param <D>         - the variable for detail bean
   * @param mSQLStr     - SQL string for the master part
   * @param dSQLStr     - SQL string for the detail part
   * @param attribStr   - the attribute value on master table
   * @param mc          - the master class name
   * @param dc          - the detail class name
   * @param fkFieldName - the foreign key field name in master
   *
   * @return an array list of the MasterDetailObject
   */
//  public static <M, D> List<MasterDetail<M, D>> getMasterDetailsByOneAttribute(
//          String mSQLStr, String dSQLStr, int attribStr,
//          Class<? extends M> mc, Class<? extends D> dc,
//          String fkFieldName)
//          throws NoSuchFieldException, IllegalArgumentException,
//                 IllegalAccessException, SQLException {
//
//    List<MasterDetail<M, D>> mdArr = new ArrayList<>();
//
//    try (Connection con = getSingleConn()) {
//      List<M> mArr = getBeans(con, mSQLStr, mc, attribStr);
//
//      for (M mBean : mArr) {
//
//        Field fkField = mBean.getClass().getDeclaredField(fkFieldName);
//        fkField.setAccessible(true);
//        int fkValue = (Integer) fkField.get(mBean);
//        List<D> dArr = (List<D>) getBeans(con, dSQLStr, dc, fkValue);
//        mdArr.add(new MasterDetail<M,D>(mBean, dArr));
//
//      } //for
//    } //try
//
//    return mdArr;
//  } //method


  /**
   * make a new bean and put the sb set contents into it.
   *
   * @Pre-condition: the sb set is on the appropriate record.
   *
   * @param rs - the sb set
   * @param e  - the bean object that will return the contents of the sb set.
   *
   * @return the new bean filled with the sb set data.
   */
  private static <E> void beanMakerEngine(ResultSet rs, E bean) {

    Field[] f = bean.getClass().getDeclaredFields();
    int index = 1;

    for (Field field : f) {

      field.setAccessible(true);
      try {
        String type = field.getType().toString();
//                System.out.println("Type = " + type);

        // private fields has modifer = 2
        if (field.getModifiers() != 2) {
          continue;
        }

        switch (type) {
          case "int":
          case "class java.lang.Integer":
            field.set(bean, new Integer(rs.getInt(index)));
            break;
          case "double":
          case "class java.lang.Double":
            field.set(bean, rs.getDouble(index));
            break;
          case "float":
          case "class java.lang.Float":
            field.set(bean, rs.getFloat(index));
            break;
          case "boolean":
            field.set(bean, rs.getBoolean(index));
            break;
          case "class java.lang.String":
            field.set(bean, rs.getString(index));
            break;
          case "class java.sql.Timestamp":
            field.set(bean, rs.getTimestamp(index));
            break;
        } //switch

        index++;

      } catch (IllegalAccessException | SQLException ex) {
        System.out.println(ex.getMessage());
      }

    } //for

  } //method


  private static void testbeanMakerEngine() {
//        Recipe r = new Recipe();
//        ResultSet rs = queryRecipeByID(101);
//        rs.next();
//        beanMakerEngine(rs, r);
    //System.out.println(r.toString());
  } //method.


  /**
   * Note that in this method, we cannot close con object because we lose rs in this way. So, I
   * recommend you use the executeQuery with con parametre and close the connection after you are
   * done with rs.
   *
   * execute a SQL statement (query)
   *
   * @param sqlStr
   *
   * @return the sb set.
   */
  public static ResultSet executeQuery(String sqlStr) {

    Connection con = getConn();
    ResultSet rs;
    rs = executeQuery(con, sqlStr);
    return rs;

  } //method


  /**
   * execute a SQL statement (query)
   *
   * @param con      - the connection object to the database
   * @param sqlStr
   *
   * @return the sb set.
   */
  public static ResultSet executeQuery(Connection con, String sqlStr) {

    try (PreparedStatement pstmnt = con.prepareStatement(sqlStr)) {
      return pstmnt.executeQuery();

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;
    }

  } //method


  /**
   * execute a SQL statement (query)
   *
   * @param con      - the connection object to the database
   * @param sqlStr
   * @param parArr
   *
   * @return the sb set.
   */
  public static ResultSet executeQueryByVarStrAttribs(
          Connection con, String sqlStr, String... parArr) {

    try (PreparedStatement pstmnt = con.prepareStatement(sqlStr)) {

      int index = 1;
      setAttribs(pstmnt, index, parArr);
      return pstmnt.executeQuery();

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;
    }

  } //method


  private static void testexecuteQuery() {
    Connection con = getConn();
    String sqlStr = "SELECT testID FROM test WHERE  testName LIKE ? ";
    String strPatt = "test%";
    ResultSet rs = executeQueryByVarStrAttribs(con, sqlStr, strPatt);
    TableUtil.printResultSet(rs);

  } //test method


  /**
   * Convert the sb set contents as beans into a hash set.
   *
   * @param <E>
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs - the sb set
   * @param c  - the class of the bean
   *
   * @return the HashSet
   */
  public static <E> Set<E> resultSetToHashSet(
          ResultSet rs, Class<? extends E> c) {

    try {
      Set<E> set = new HashSet<>();

      while (rs.next()) {
        E bean = c.newInstance();
        beanMakerEngine(rs, bean);
        set.add(bean);

      } //while

      return set;

    } catch (SQLException | InstantiationException |
            IllegalAccessException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * Read the first column of sb set and put them in an array list.
   *
   * @pre-condition: the first column MUST be integer;
   *
   * @param rs - the sb set
   *
   * @return the arrayList
   */
  public static List<Integer> resultSetFirstColToIntArr(ResultSet rs) {

    List<Integer> arr = new ArrayList<>();

    try {
      while (rs.next()) {
        arr.add(rs.getInt(1));
      }

    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ArrayList<>(0);
    }

    return arr;

  } //method


  /**
   * make a string of all values of an array separated by comma.
   *
   * @param valueArr - the input array.
   *
   * @return - the string of the array values.
   */
  private static String createCommaSeparatedStr(
          List<String> NameArr, List<String> valueArr, List<String> typeArr) {

    StringBuilder result = new StringBuilder();

    for (int i = 0; i < NameArr.size(); i++) {

            //Test:System.out.println("typeArr" + i + " = " + typeArr.get(i));
      if (valueArr.get(i).toUpperCase().equals("NULL")) {
        continue;
      } else {
        if (typeArr.get(i).equals("int")
            && Integer.valueOf(valueArr.get(i)) < 0) { //auto-increment
          continue;

        } else {
          result.append(", ").append(NameArr.get(i));
        }
      } //else
    } //for

    return result.toString().substring(2);

  } //method


  /**
   * make a string of all values of an array separated by comma. It looks at the corresponding
   * numOrStr and if it is 0 means that the value is a String. So, it wrapped it in single quotes.
   *
   * @param valueArr - the input array.
   *
   * @return - the string of the array values.
   */
  private static String createCommaSeparatedStrWrapped(List<String> valueArr, List<String> typeArr)
          throws ParseException {

    StringBuilder result = new StringBuilder();

    for (int i = 0; i < valueArr.size(); i++) {

      // remove \' from the char types
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "''"));

            //Test:System.out.println("valueArr" + i + " = " + valueArr.get(i));
      if (valueArr.get(i).equalsIgnoreCase("null")) {
        continue;
      } else {
        if (typeArr.get(i).equalsIgnoreCase("int")
            && Integer.valueOf(valueArr.get(i)) < 0) { //auto-increment
          continue;

        } else {
          if (!typeArr.get(i).equalsIgnoreCase("int") && !typeArr.get(i).equalsIgnoreCase(
                  "boolean")) {
            result.append(", \'").append(valueArr.get(i)).append("\'");

          } else {
            result.append(", ").append(valueArr.get(i));
          }
        }
      }

    } //for

        //Test: System.out.println("Value = " + sb.toString().substring(2));
    return result.toString().substring(2);

  } //method


  /**
   * create INSERT statement
   *
   * @param tabName - the table name.
   * @param conName - the array of the columns names.
   * @param values  - the values array
   *
   * @return the INSERT statement.
   */
  private static String createINSERTStatement(
          String tabName, List<String> nameArr,
          List<String> valueArr, List<String> typeArr) {
    try {
      String colStr = createCommaSeparatedStr(nameArr, valueArr, typeArr);
      String valStr = createCommaSeparatedStrWrapped(valueArr, typeArr);

      String insertStr = "INSERT INTO %s (%s) \nVALUES (%s);";

      return String.format(insertStr, tabName, colStr, valStr);
    } catch (ParseException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  private static void testcreateINSERTStatement() {
    String tabName = "test";
    ArrayList<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    ArrayList<String> valueArr = new ArrayList<>();
    valueArr.add("25");
    valueArr.add("ahmad");
    valueArr.add("-34");

    ArrayList<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("String");
    typeArr.add("timestamp");

    System.out.println(
            createINSERTStatement(tabName, nameArr, valueArr, typeArr));

  } //method


  /**
   * Insert a given bean into the appropriate tables.
   *
   * @param <E>
   * @param con
   * @param bean - the given bean object
   *
   * @return true if the insert operation is successful and false otherwise
   */
  public static <E> boolean beanInsertEngine(
          Connection con, E bean) {

    String tabName = bean.getClass().getSimpleName();

    List<String> fieldNameArr = getBeanFieldName(bean);
    List<String> fieldTypeArr = getBeanFieldType(bean);
    List<String> fieldValueArr = getBeanFieldValue(bean);

    String sqlStr = createINSERTStatement(
            tabName, fieldNameArr, fieldValueArr, fieldTypeArr);

        //Test: System.out.println("sqlStr = " + sqlStr);
    try (Statement stmnt = con.createStatement()) {

      int result = stmnt.executeUpdate(sqlStr);

      if (result > 0) {
        return true;
      } else {
        return false;
      }

    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    }

  } //method


  private static void testbeanInsertEngine() {

//        Entry entryBean = new Entry(
//            20, "Student", "someone who goes to school", "Daneshjoo", "", "7,8");
//        System.out.println(entryBean);
//
//        System.out.println(beanInsertEngine(entryBean));
  } //method


  /**
   * make a statement for UPDATE.
   *
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the type array
   * @param pkName   - the primary key name
   * @param tabName  - the table name
   *
   * @return the set clause.
   */
  private static String createUpdateClause(
          List<String> nameArr,List<String> valueArr,
          List<String> typeArr, String pkName, String tabName) {

    StringBuilder sb = new StringBuilder();

    String setStr = null;
    String whereStr = null;

    for (int i = 0; i < valueArr.size(); i++) {
      // remove \' from the char types
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "\'"));

            //Test: System.out.println("\nname = " + nameArr.get(i));
      //Test: System.out.println("type = " + typeArr.get(i));
      //Test: System.out.println("value = " + valueArr.get(i));
      //Assumes that the PK is none-string (=int)
      if (nameArr.get(i).equalsIgnoreCase(pkName)) {
        whereStr = String.format("WHERE %s = %s",
                nameArr.get(i), valueArr.get(i));
        continue;

      } //if

      switch (typeArr.get(i)) {
        case "int":
        case "class java.lang.Integer":
          if (!valueArr.get(i).equals("-1")) {
            setStr = String.format(", %s = %s\n",
                    nameArr.get(i), valueArr.get(i));
          }
          break;
        case "double":
        case "float":
        case "class java.lang.Float":
        case "class java.lang.Double":
        case "class java.lang.Boolean":
        case "boolean":
          setStr = String.format(", %s = %s\n",
                  nameArr.get(i), valueArr.get(i));
          break;
        case "class java.lang.String":
          if (valueArr.get(i).isEmpty() || valueArr.get(i).equalsIgnoreCase("null")) {
            setStr = String.format(", %s = null\n", nameArr.get(i));
          } else {
            setStr = String.format(", %s = \'%s\'\n",
                    nameArr.get(i), valueArr.get(i));
          }
          break;

        /* If you put the timestamp null, then it update it by default
         * by the current date-time. That's why it should be updated by
         * the previous value.*/
        case "class java.sql.Timestamp":
          if (valueArr.get(i).equalsIgnoreCase("null")) {
            setStr = String.format(", %s = %s\n",
                    nameArr.get(i), nameArr.get(i));
          } else {
            setStr = String.format(", %s = TIMESTAMP \'%s\'\n",
                    nameArr.get(i), valueArr.get(i));
          }
          break;
      } //switch

            //Test: System.out.println("setStr = " + setStr);
      if (setStr != null) {
        sb.append(setStr);
      }
      setStr = null;

    } //for

    setStr = sb.toString().substring(2);
    return String.format("UPDATE %s \nSET %s %s", tabName, setStr, whereStr);

  } //method


  private static void testcreateUpdateClause() {

    ArrayList<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    ArrayList<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("class java.lang.String");
    typeArr.add("int");

    ArrayList<String> valueArr = new ArrayList<>();
    valueArr.add("25");
    valueArr.add("");
    valueArr.add("-1");

    String tabName = "test";
    String pkName = "a";

    System.out.println(createUpdateClause(
            nameArr, valueArr, typeArr, pkName, tabName));

  } //method


  /**
   * get the bean fields Names.
   *
   * @param e - the bean object.
   */
  private static <E> ArrayList<String> getBeanFieldName(E bean) {
    ArrayList<String> arr = new ArrayList<>();

    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      field.setAccessible(true);
      arr.add(field.getName());
    } //for

    return arr;

  } //method

//    private static void testgetBeanFieldName() {
//        Recipe r = new Recipe("Good Name", "Every Thing Mixed",
//                "Every thing", 1, "image");
//
//        ArrayList<String> arr = getBeanFieldName(r);
//
//        System.out.println(arr.toString());
//
//    } //test method

  /**
   * Get the bean fields Names, create a comma delimited string of them.
   *
   * @param <E>
   * @param bean - the bean object.
   *
   * @return the comma separated string of the fields names
   */
  public static <E> String getBeanFieldsNamesCommaSeparated(E bean) {

    StringBuilder sb = new StringBuilder();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      sb.append(field.getName()).append(",");
//            System.out.println("fieldName = " + field.getName());
    }

    return sb.toString().substring(0, sb.toString().length() - 1);

  } //method


  private static void testgetBeanFieldsNamesCommaSeparated() {

//        System.out.println(getBeanFieldsNamesCommaSeparated(new Entry()));
  } //test method


  /**
   * Count number of beans in a table by one integer and variable number of String attributes
   *
   * @param sqlStr - the SQL string
   * @param a      - the first parameter
   * @param parArr - variable number of string attributes
   *
   * @return the count number
   */
  public static Integer countBeansByIntVarStrAttribs(
          String sqlStr, int a, String... parArr) {

    ResultSet rs = null;

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      pstmt.setInt(index++, a);
      setAttribs(pstmt, index, parArr);
      rs = pstmt.executeQuery();
      rs.next();

      return rs.getInt(1);

    } catch (SQLException e) {
      e.printStackTrace();
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  /**
   * Count number of beans in a table by one String and variable number of integer attributes
   *
   * @param sqlStr - the SQL string
   * @param a      - the first parameter
   * @param parArr - variable number of integer attributes
   *
   * @return the count number
   */
  public static Integer countBeansByStrVarIntAttribs(
          String sqlStr, String a, int... parArr) {

    ResultSet rs = null;

    try (
            Connection con = getConn();
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      pstmt.setString(index++, a);
      setAttribs(pstmt, index, parArr);
      rs = pstmt.executeQuery();
      rs.next();

      return rs.getInt(1);

    } catch (SQLException e) {
      e.printStackTrace();
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  private static void testcountBeansByStrVarIntAttribs() {

//        String sqlStr = SQL.COUNT_MODE_2;
//        System.out.println(sqlStr);
//
//        String catList = "5";
//        int vocabID = 20;
//
//        System.out.println(
//            countBeansByStrVarIntAttribs(sqlStr, catList, vocabID));
  } //test method


  /**
   * A generic method to check if an attribute exists?
   *
   * @param sqlStr - the SQL string
   * @param parArr - the array of integer parameters
   *
   * @return true if the attribute exists and false otherwise
   */
  public static boolean existanceChaeckByVarIntAttribs(
          String sqlStr, int... parArr) {

    Connection con = getConn();
    boolean result = existanceChaeckByVarIntAttribs(con, sqlStr, parArr);
    freeResource(con);
    return result;

  } //method


  /**
   * A generic method to check if an attribute exists?
   *
   * @param con    - the connection to the database object
   * @param sqlStr - the SQL string
   * @param parArr - the array of integer parameters
   *
   * @return true if the attribute exists and false otherwise
   */
  public static boolean existanceChaeckByVarIntAttribs(
          Connection con, String sqlStr, int... parArr) {

    ResultSet rs = null;

    try (
            PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      int index = 1;
      setAttribs(pstmt, index, parArr);

      rs = pstmt.executeQuery();

      return rs.next();

    } catch (SQLException e) {
      e.printStackTrace();
      return false;

    } finally {
      freeResource(rs);
    }

  } //method


  private static void testexistanceChaeckByVarIntAttribs() {
//        String sqlStr = SQL.CAT_EXIST_BY_CATID;
//        int catID = 15;
//        System.out.println(existanceChaeckByVarIntAttribs(sqlStr, catID));

  } //method


  public static <E> boolean beanUpdateEngine(
          Connection con, E bean, String pkName) {

    String tabName = bean.getClass().getSimpleName();

    List<String> nameArr = getBeanFieldName(bean);
    List<String> typeArr = getBeanFieldType(bean);
    List<String> valueArr = getBeanFieldValue(bean);

    String stmnt = createUpdateClause(nameArr, valueArr, typeArr, pkName, tabName);

        //Test: System.out.println("update Statement: \n" + stmnt);
    int result = executeUpdate(con, stmnt);

    if (result == -1) {
      return false;
    } else {
      return true;
    }

  } //method


  /**
   * get two beans and update the corresponding record from the table.
   *
   * @param <E>
   * @param bean
   * @param pkName
   *
   * @return true if the update operation is successful and false otherwise.
   */
  public static <E> boolean beanUpdateEngine(E bean, String pkName) {

    Connection con = getConn();
    boolean result = beanUpdateEngine(con, bean, pkName);
    freeResource(con);

    return result;

  } //method


  private static void testbeanUpdateEngine() {

    Connection con = getConn();
    String pkName = "entryID";
    int entryID = 1;
    int vocabID = 20;
    String entry = "teacher";
    String meaning = "This is another test for teacher.";
    String trans = null;
    String example = "You are a teacher.";
    String catList = "7,8";
//        Entry bean = new Entry(vocabID, entryID, entry,
//            meaning, trans, example, catList);
//
//        boolean result = beanUpdateEngine(con, bean, pkName);
//
//        System.out.println(result);
    freeResource(con);

  } //method


  /**
   * A generic method to set PreparedStatment object attributes.
   *
   * @param pstmt  - the PreparedStatment object
   * @param parArr - the array of parameters of any type (Integer, String)
   */
  public static void setAttribs(PreparedStatement pstmt, Object... parArr) {

    int index = 1;

    try {
      for (Object o : parArr) {
        if (o instanceof Integer) {
          pstmt.setInt(index, Integer.valueOf(o.toString()));
        } else if (o instanceof String) {
          pstmt.setString(index, o.toString());
        }

        index++;
      } //for

    } catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
    }

  } //method


  /**
   * A generic method to get a bean by variable number of attributes
   *
   * @param <E>
   * @param sqlStr - the SQL string
   * @param c      - the class of the object
   * @param parArr - variable number of attributes
   *
   * @return the bean
   */
  public static <E> E getBean(
          String sqlStr, Class<? extends E> c, Object... parArr) {

    ResultSet rs = null;

    try (Connection con = getConn();
         PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      setAttribs(pstmt, parArr);
      rs = pstmt.executeQuery();
      return resultSet2Obj(rs, c);

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  private static void testgetBean() {

    String sqlStr = "SELECT * FROM EntryW "
                    + "WHERE entry   LIKE ? "
                    + "AND   meaning LIKE ? "
                    + "AND   trans   LIKE ? "
                    + "AND   example LIKE ? "
                    + "AND   vocabID = ? "
                    + "ORDER BY regDate DESC LIMIT ?, 1";

    int vocabID = 20;
    int recNum = 0;
    String entry = "maim";
    String meaning = "";
    String trans = "";
    String example = "";

//        Entry entryw = SqlBak.getBean(
//            sqlStr, Entry.class, vocabID, recNum,
//            entry, meaning, trans, example);
//        System.out.println(entryw);
  } //test method


  /**
   * a generic method to get an array list of beans by one int attribute
   *
   * @param <E>
   * @param sqlStr - the SQL string
   * @param parArr
   * @param c      - the class of the object
   *
   * @return the beans list
   */
  public static <E> List<E> getBeans(
          Class<? extends E> c, String sqlStr, Object... parArr) {

    try (Connection con = getConn()) {
      return getBeans(con, sqlStr, c, parArr);

    } catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * a generic method to get an array list of beans by one int attribute
   *
   * @param <E> the type parameter
   * @param con the connection object to database
   * @param sqlStr - the SQL string
   * @param c - the class of the database object
   * @param parArr the array of the parameters
   *
   * @return the array list of the the beans
   */
  public static <E> List<E> getBeans(Connection con, String sqlStr,
                                     Class<? extends E> c, Object... parArr) {

    ResultSet rs = null;

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr)) {

      setAttribs(pstmt, parArr);
      rs = pstmt.executeQuery();

      return resultSetToArrayList(rs, c);

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  private static void setAttribsTests(Object... parArr) {

    for (Object o : parArr) {
      System.out.println(o);
      if (o instanceof String) {
        System.out.println("It is String!");
      }
      if (o instanceof Integer) {
        System.out.println("It is Integer!");
      }

    }
  } //method


  private static void testsetAttribs() {

    Object[] parArr = {
      new Integer(4), "Ahmad"
    };

    setAttribsTests(parArr);
    setAttribsTests("Ahmad2");
    setAttribsTests(3, "Ali");
    setAttribsTests();

  } //method


  /**
   */
//........................ M A I N   M E T H O D ............................//
  /**
   * This main method is just for testing this class.
   *
   * @param args the arguments
   */
  public static void main(String... args) {

    testsetAttribs();

  } //main method

} //class
